package com.gms.server.service;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.json.JSONWriter;

import com.gms.server.model.City;
import com.gms.tool.ModelToJsonFormat;
import com.gms.tool.ShareData;

public class CheckCityPopulation extends HttpServlet {

	/**
	 * 
	 */
	private static final long serialVersionUID = 6521622349260004958L;

	
	public CheckCityPopulation() {
	}
	
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

		String cityName = request.getParameter("cityName");
		City city = new City(cityName);

		JSONWriter writer = new JSONWriter(response.getWriter());
		try {
			City cityChecked = this.checkCityPopulation(city);
			// Check is user is null. Because if user is name.
			if (cityChecked == null) {
				// If user is null we send and string to let know user is null
				writer.object();
				writer.key("loginStatus").value("no");
				writer.endObject();
			} else {
				// Else user is not null parse it

				// Transforme a model user to parson object
				ModelToJsonFormat.cityToJson(cityChecked, writer);
			}

		} catch (Exception e) {

			e.printStackTrace();
		}

	}

	/**
	 * Get an Instance SQL connection
	 * 
	 * @return Object Connection
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	private Connection getConnect() throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
		
		Class.forName("com.mysql.jdbc.Driver").newInstance();
		
		return DriverManager.getConnection("jdbc:mysql://" + ShareData.ADDRESS + "/" + ShareData.DBNAME, ShareData.DBLOGIN, ShareData.DBPASSWORD);
	}

	/**
	 * Verifie the user parameter if its correct
	 * 
	 * @param city
	 *            : User to check
	 * @return the user checked
	 * @throws Exception
	 * @throws IllegalAccessException
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	private City checkCityPopulation(City city) throws Exception, IllegalAccessException, ClassNotFoundException, SQLException {

		City cityReceived = null;
		
		String requestSQL = "select populationTotale from french_people where nomCommune like '" + city.getName() + "'";
		
		Connection connection = getConnect();
		
		Statement statement = connection.createStatement();
		
		ResultSet resultSet = statement.executeQuery(requestSQL);

		while (resultSet.next()) {
			cityReceived = new City(city.getName(), resultSet.getInt("populationTotale"));
		}
		return cityReceived;
	}
	
}
